/**
 * author:Administrator
 * date:2013-5-21
 */
package com.roc.web.action.agent;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.pojo.Operators;
import com.roc.util.Constance;
import com.roc.util.constant.Constant;
import com.roc.util.constant.Role;

/**
 * 
 * @author 陈锴
 * 
 * 代理商查询
 * 
 * 2014-08-17
 *
 */
public class AgentQuerySearchCondition extends BaseSearchCondition {
	

	private String orgCode;
	private String accountNo;
	private String realName;
	private String cardNo;
	private String mobile;
	private String status;       //代理商状态
	private String role;   //角色类型
	
	private String roleId; // 当前登陆账号角色id
	
	private Operators operators;  //操作员
	
	private String isDirectly;   //是否直属代理商
	private String 	opId; // 业务员
	
//	private Object[] obj;

	private String agentId;// 代理商id

	private String minDate; // 创建开始时间(年月日)
	private String maxDate; // 创建结束时间(年月日)

	private String oneOrgCode;// 一级机构管理员
	
	private String groupCd; // 商户类别，页面查询条件

	private String groupCdFlag; //受理市场标识
	
	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getOrderPartOfHQL()
	 */
	@Override
	protected String getOrderPartOfHQL() {
		// TODO Auto-generated method stub
		
		return "   ";
	}

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getRowCountHQL()
	 */
	@Override
	protected String getRowCountHQL() {
		StringBuffer sb = new StringBuffer();
		sb.append( " select count(*) from T_operators o  " );

		// 查询账号角色是 系统管理员 
		if ( roleId.equals(Role.SYSTEM.getCode()) ) {
			sb.append( " where 1 = 1 and o.org_code = " + orgCode  );
		}
		
		// 查询账号角色是 机构管理员 
		if ( roleId.equals(Role.O_MANAGER.getCode()) ) {
			sb.append( " where o.ONEAGENTID = " + oneOrgCode );
//			sb.append(" and o.id != '" + agentId + "' " );
		}

		// 查询账号角色是 代理商 
		if ( roleId.equals(Role.AGENT.getCode()) ) {

			sb.append(	  ", (SELECT TO_CHAR(T.ID) TID "  );
			sb.append(     "         FROM T_OPERATORS T " );
			sb.append("       START WITH ID = "+agentId+" " );
	//	    sb.append("              AND T.ROLE_TYPE = '03' " );
			sb.append("     CONNECT BY PRIOR T.ID = T.SUPRI_ID " );
		    sb.append("              AND T.ROLE_TYPE in ( '02', '03', '16' ) " );
			// 如果地区登陆角色是 收单事业部 ，查询所有受理市场运营角色
			if ( null !=agentId && agentId.equals(Constant.SHOUDANSHIYEBU_ID) ) {
				sb.append("      union" );
				sb.append("      SELECT TO_CHAR(T2.ID) TID" );
				sb.append("        FROM T_OPERATORS T2" );
				sb.append("       where T2.ROLE_TYPE = '" + Role.OPERATORS_LEAD.getCode() + "'" );
			} 
			sb.append("       ) TT " );
			
			sb.append(" where  o.id = TT.TID  " );
	//		sb.append(" and o.id != '" + agentId + "' " );
		}
		// 查询账号角色是 市场直营岗/市场主管
		if ( roleId.equals(Role.MARKET_DIRECT.getCode()) || roleId.equals(Role.MARKET_LEAD.getCode()) ) {

			sb.append(" where exists (select 1 from t_oper_role ar  where ar.agentid = o.ONEAGENTID and ar.operid =" + agentId + ")  " ); 
			
			/*
				sb.append(", (  select distinct t.id TID , t.real_name " );
					sb.append("   from t_operators t, " );
					sb.append("        (select r.agentid id from t_oper_role r where r.operid = " + agentId + ") r1 " );
					sb.append("  where t.role_type = '03' " );
					sb.append("  START WITH t.id = r1.id " );
					sb.append(" CONNECT BY t.supri_id = PRIOR t.id ) TT " );*/
							
		}
		
		return sb.toString();
	}

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getSearchHQL()
	 */
	@Override
	protected String getSearchHQL() {
		StringBuffer sb = new StringBuffer();
	//	sb.append( "select o.id , o.account_no , o.real_name , o.sex , o.mobile , o.orgCode , o.role_type , o.supri_id , o.card_no , o.status , o.COMPANYID , o.ONEAGENTID , o.tax_registration_certificate , o.organization_code from T_operators o  " );
		
		sb.append( " select o.id, "  );
		sb.append( "        o.account_no, "  );
		sb.append( "        o.real_name, "  );
		sb.append( "        o.sex, "  );
		sb.append( "        o.mobile, "  );
		sb.append( "        o.org_code, "  );
		sb.append( "        o.role_type, "  );
		sb.append( "        o.supri_id, "  );
		sb.append( "        o.card_no, "  );
		sb.append( "        o.status, "  );
		sb.append( "        o.COMPANYID, "  );
		sb.append( "        o.ONEAGENTID, "  );
		sb.append( "        o.tax_registration_certificate, "  );
		sb.append( "        o.organization_code, "  );

		sb.append( "        (select T1.Real_Name "  );
		sb.append( "           from T_OPERATORS T1 "  );
		sb.append( "          where T1.ROLE_TYPE in ('00', '02', '03') "  );
		sb.append( "            and T1.id = o.supri_id) supri_name, "  );
		sb.append( "        (select A1.Name "  );
		sb.append( "           from tbl_area A1 "  );
		sb.append( "          where A1.Ins_Code = o.org_code "  );
		sb.append( "            and A1.id = o.companyid) companyname, "  );
		       
		sb.append( "        (select T1.Real_Name "  );
		sb.append( "           from T_OPERATORS T1 "  );
		sb.append( "          where T1.ROLE_TYPE in ('02') "  );
		sb.append( "            and T1.oneagentid = o.oneagentid) oneagentname "  );
		
		sb.append( "   from T_operators o "  );

		// 查询账号角色是 系统管理员 
		if ( roleId.equals(Role.SYSTEM.getCode()) ) {
			sb.append( " where 1 = 1 and o.org_code = " + orgCode  );
		}
		
		// 查询账号角色是 机构管理员 
		if ( roleId.equals(Role.O_MANAGER.getCode()) ) {
			sb.append( " where o.ONEAGENTID = " + oneOrgCode );
		//	sb.append(" and o.id != '" + agentId + "' " );
		}

		// 查询账号角色是 代理商 
		if ( roleId.equals(Role.AGENT.getCode()) ) {

			sb.append(	  ", (SELECT TO_CHAR(T.ID) TID "  );
			sb.append(     "         FROM T_OPERATORS T " );
			sb.append("       START WITH ID = " + agentId + " " );
		//    sb.append("              AND T.ROLE_TYPE = '03' " );
			sb.append("     CONNECT BY PRIOR T.ID = T.SUPRI_ID " );
		    sb.append("              AND T.ROLE_TYPE in ( '02', '03', '16' ) " );
			// 如果地区登陆角色是 收单事业部 ，查询所有受理市场运营角色
			if ( null !=agentId && agentId.equals(Constant.SHOUDANSHIYEBU_ID) ) {
				sb.append("      union" );
				sb.append("      SELECT TO_CHAR(T2.ID) TID" );
				sb.append("        FROM T_OPERATORS T2" );
				sb.append("       where T2.ROLE_TYPE = '" + Role.OPERATORS_LEAD.getCode() + "'" );
			} 
			sb.append("       ) TT " );
			
			sb.append(" where  o.id = TT.TID  " );
		//	sb.append(" and o.id != '" + agentId + "' " );
		}
		// 查询账号角色是 市场直营岗/市场主管
		if ( roleId.equals(Role.MARKET_DIRECT.getCode()) || roleId.equals(Role.MARKET_LEAD.getCode()) ) {
			/*sb.append("  and ( exists (select 1 "); 
			sb.append("  from TBL_MER_AGENT_RELATION ar, t_oper_role o1  where o1.agentid = ar.agentid and a.mcht_cd=ar.merno  AND o1.operid =").append(loginId).append(" )");         
			sb.append(" or a.REC_UPD_OPR=").append(loginId).append(" )");           
			*/

			sb.append(" where exists (select 1 from t_oper_role ar  where ar.agentid = o.ONEAGENTID and ar.operid =" + agentId + ")  " ); 
			 
			/*
			
			sb.append(", (  select distinct t.id TID , t.real_name " );
			sb.append("   from t_operators t, " );
			sb.append("        (select r.agentid id from t_oper_role r where r.operid = " + agentId + ") r1 " );
			sb.append("  where t.role_type = '03' " );
			sb.append("  START WITH t.id = r1.id " );
			sb.append(" CONNECT BY t.supri_id = PRIOR t.id ) TT " );*/
			
		}
		
		return sb.toString();
	}

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getWhereHQLFieldBeanList()
	 */
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();

		//系统管理员
//		if(operators != null){
//			
//			//代理商查询自己下级代理商
//			if(operators.getRoleType().equals(Role.AGENT.getCode())){
//				filedBeanList.add(new WhereHQLFieldBean("o.superiorId", operators.getId(), "=", false));
//			}
//			
//		}
		
		if (Constance.isNotEmpty(orgCode)) {
			filedBeanList.add(new WhereHQLFieldBean("o.org_code", orgCode.trim(), "=", false));
		}
		
		if (Constance.isNotEmpty(accountNo)) {
			filedBeanList.add(new WhereHQLFieldBean("o.account_no", accountNo.trim(), "=", false));
		}

		if (Constance.isNotEmpty(realName)) {
			filedBeanList.add(new WhereHQLFieldBean("o.real_name", realName.trim(),
					"LIKE", false, "ALL"));
		}
		if (Constance.isNotEmpty(mobile)) {
			filedBeanList.add(new WhereHQLFieldBean("o.mobile", mobile.trim(), "=", false));
		}
		if (Constance.isNotEmpty(cardNo)) {
			filedBeanList.add(new WhereHQLFieldBean("o.card_no", cardNo.trim(), "=", false));
		}
		
		if (Constance.isNotEmpty(status)) {
			filedBeanList.add(new WhereHQLFieldBean("o.status", status, "=", false));
		}

		if (Constance.isNotEmpty(opId)) {
			filedBeanList.add(new WhereHQLFieldBean("o.SUPRI_ID", new Long(opId.trim()), "=", false));
		}
/*		if(obj != null){
			filedBeanList.add(new WhereHQLFieldBean("o.id", obj, "IN", false));
		}*/
		String df_role_type = "02,03,16";// 查询 默认角色类型
		// 如果地区登陆角色是 收单事业部 ，查询所有受理市场运营角色
		if ( roleId.equals(Role.AGENT.getCode()) && null !=agentId && agentId.equals(Constant.SHOUDANSHIYEBU_ID) ) {
			df_role_type = "02,03,16,18";
		}
		// 查询 机构+代理商+业务员
		if ( role.equals("99")  ){ 
			filedBeanList.add(new WhereHQLFieldBean(" o.ROLE_TYPE IN (" + df_role_type + ") "));// 查询机构管理员和代理商
		} else if ( role.equals(Role.AGENT.getCode())  ){ 
			//如果查询角色为 代理商，查询02,03角色
			filedBeanList.add(new WhereHQLFieldBean(" o.ROLE_TYPE IN (02,03) "));// 查询机构管理员和代理商
		}else {
			filedBeanList.add(new WhereHQLFieldBean(" o.ROLE_TYPE IN ("+role+") "));
		}

		if (Constance.isNotEmpty(minDate) && Constance.isNotEmpty(maxDate)) {
			String startDate = minDate.replace("-", "");
			String endDate = maxDate.replace("-", "");
			filedBeanList.add(new WhereHQLFieldBean( "to_char(o.DT_CREATE,'yyyyMMdd')", startDate, ">=", false));
			filedBeanList.add(new WhereHQLFieldBean( "to_char(o.DT_CREATE,'yyyyMMdd')", endDate, "<=", false));
		}
		if (Constance.isNotEmpty(groupCd)) {
			filedBeanList.add(new WhereHQLFieldBean("o.GROUP_CD", new Long(groupCd.trim()), "=", false));
		}
		if (Constance.isNotEmpty(groupCdFlag)) {
			filedBeanList.add(new WhereHQLFieldBean("o.GROUP_CD", new Long(groupCdFlag.trim()), "=", false));
		}
		return filedBeanList;
	}

	/**
	 * @return the accountNo
	 */
	public String getAccountNo() {
		return accountNo;
	}

	/**
	 * @param accountNo the accountNo to set
	 */
	public void setAccountNo(String accountNo) {
		this.accountNo = accountNo;
	}

	/**
	 * @return the realName
	 */
	public String getRealName() {
		return realName;
	}

	/**
	 * @param realName the realName to set
	 */
	public void setRealName(String realName) {
		this.realName = realName;
	}

	/**
	 * @return the cardNo
	 */
	public String getCardNo() {
		return cardNo;
	}

	/**
	 * @param cardNo the cardNo to set
	 */
	public void setCardNo(String cardNo) {
		this.cardNo = cardNo;
	}

	public String getOrgCode() {
		return orgCode;
	}

	public void setOrgCode(String orgCode) {
		this.orgCode = orgCode;
	}

	public String getMobile() {
		return mobile;
	}

	public void setMobile(String mobile) {
		this.mobile = mobile;
	}

	public String getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public String getRole() {
		return role;
	}

	public void setRole(String role) {
		this.role = role;
	}

	public Operators getOperators() {
		return operators;
	}

	public void setOperators(Operators operators) {
		this.operators = operators;
	}

	public String getIsDirectly() {
		return isDirectly;
	}

	public void setIsDirectly(String isDirectly) {
		this.isDirectly = isDirectly;
	}

	public String getOpId() {
		return opId;
	}

	public void setOpId(String opId) {
		this.opId = opId;
	}

	public String getAgentId() {
		return agentId;
	}

	public void setAgentId(String agentId) {
		this.agentId = agentId;
	}

	public String getMinDate() {
		return minDate;
	}

	public void setMinDate(String minDate) {
		this.minDate = minDate;
	}

	public String getMaxDate() {
		return maxDate;
	}

	public void setMaxDate(String maxDate) {
		this.maxDate = maxDate;
	}

	public String getRoleId() {
		return roleId;
	}

	public void setRoleId(String roleId) {
		this.roleId = roleId;
	}

	public String getOneOrgCode() {
		return oneOrgCode;
	}

	public void setOneOrgCode(String oneOrgCode) {
		this.oneOrgCode = oneOrgCode;
	}

	public String getGroupCd() {
		return groupCd;
	}

	public void setGroupCd(String groupCd) {
		this.groupCd = groupCd;
	}

	public String getGroupCdFlag() {
		return groupCdFlag;
	}

	public void setGroupCdFlag(String groupCdFlag) {
		this.groupCdFlag = groupCdFlag;
	}

}
